﻿using MySql.Data.MySqlClient;
using System.Data;
using System.Text;

namespace EmployeeLeavePackage
{
    public class LeavePackage
    {
        public async Task Test()
        {
#if DEBUG
            var connectionString = "server=10.12.10.20;port=3307;user=ihcm;password=iHcm2023@mysql;database=ihcm_combine";
#else
    Console.WriteLine("请输入数据库server:");
    var server = Console.ReadLine();
    Console.WriteLine("请输入数据库port:");
    var port = Console.ReadLine();
    Console.WriteLine("请输入数据库:");
    var db = Console.ReadLine();
    Console.WriteLine("请输入数据库user:");
    var user = Console.ReadLine();
    Console.WriteLine("请输入数据库password:");
    var password = Console.ReadLine();
    var connectionString = $"server={server};port={port};user={user};password={password};database={db}";
#endif
            Console.WriteLine($"--------------------【{connectionString}】数据库连接中--------------------");
            using var connection = new MySqlConnection(connectionString);
            var employees = new List<Employee>();
            var leaves = new List<Leave>();
            try
            {
                await connection.OpenAsync();
                Console.WriteLine("--------------------数据库连接成功--------------------");
                Console.WriteLine("--------------------开始获取员工数据--------------------");
                var employeeSql = "SELECT id,employee_number FROM employees ";
                using var employeeCommand = new MySqlCommand(employeeSql, connection);
                using (var employeeReader = await employeeCommand.ExecuteReaderAsync())
                {
                    while (await employeeReader.ReadAsync())
                    {
                        var employee = new Employee
                        {
                            Id = employeeReader.GetInt32("id"),
                            EmployeeNumber = employeeReader.IsDBNull(ordinal: employeeReader.GetOrdinal("employee_number")) ? null : employeeReader.GetString("employee_number")
                        };
                        employees.Add(employee);
                    }
                }
                Console.WriteLine($"--------------------获取员工数据完毕，共 {employees.Count} 条--------------------");

                Console.WriteLine("--------------------开始获取休假政策数据--------------------");
                var leaveSql = "SELECT id,code,name,leave_type_ids FROM time_leave_policies ";
                using var leaveCommand = new MySqlCommand(leaveSql, connection);
                using (var leaveReader = await leaveCommand.ExecuteReaderAsync())
                {
                    while (await leaveReader.ReadAsync())
                    {
                        var leave = new Leave
                        {
                            Id = leaveReader.GetInt32("id"),
                            Code = leaveReader.IsDBNull(ordinal: leaveReader.GetOrdinal("code")) ? null : leaveReader.GetString("code"),
                            Name = leaveReader.IsDBNull(ordinal: leaveReader.GetOrdinal("name")) ? null : leaveReader.GetString("name"),
                            LeaveTypeIds = leaveReader.IsDBNull(ordinal: leaveReader.GetOrdinal("leave_type_ids")) ? null : leaveReader.GetString("leave_type_ids")
                        };
                        leaves.Add(leave);
                    }
                }
                Console.WriteLine($"--------------------获取休假政策数据完毕，共 {leaves.Count} 条--------------------");

#if DEBUG
                var path = "C:\\Users\\Stephen Shu\\Desktop\\Employee Leave Package.xlsx";
#else
    Console.WriteLine("请输入Excel文件地址:");
    var path = Console.ReadLine();
#endif
                Console.WriteLine("--------------------开始获取Excel数据--------------------");
                var employeeLeaves = ExcelHelper.Read(path);
                Console.WriteLine($"--------------------获取Excel数据完毕，共 {employeeLeaves.Count} 条--------------------");

                Console.WriteLine("--------------------开始生成导入数据--------------------");
                var result = new List<Result>();
                foreach (var item in employeeLeaves)
                {
                    var employeeTemp = employees.FirstOrDefault(x => x.EmployeeNumber.Equals(item.EmployeeNumber.Trim()));
                    if (employeeTemp == null)
                        continue;
                    var leaveTemp = leaves.FirstOrDefault(x => x.Code.Equals(item.Code.Trim()));
                    if (leaveTemp == null)
                        continue;
                    if (string.IsNullOrEmpty(item.Date))
                        continue;
                    var temp = new Result
                    {
                        EmployeeId = employeeTemp.Id,
                        LeavePolicyId = leaveTemp.Id,
                        LeavePolicyName = leaveTemp.Name,
                        EffectiveDate = item.Date,
                        EndDate = item.EndDate,
                        LeaveTypeIds = leaveTemp.LeaveTypeIds
                    };
                    result.Add(temp);
                }
                Console.WriteLine($"--------------------生成导入数据完毕，共 {result.Count} 条--------------------");

                if (result.Count > 0)
                {
                    Console.WriteLine("是否生成导入sql？[Y/N]");
                    var createSql = Console.ReadLine();
                    if (createSql == "Y" || createSql == "y")
                    {
                        Console.WriteLine("                   ");
                        var sb = new StringBuilder($"DELETE FROM time_leave_policy_employee_details WHERE employee_id in ({string.Join(",", result.Select(x => x.EmployeeId).ToArray())});");
                        sb.AppendLine("INSERT INTO time_leave_policy_employee_details (company_id, employee_id, leave_policy_id, leave_policy_name, effective_date, end_date, leave_type_ids, created_at) VALUES ");
                        foreach (var item in result)
                        {
                            sb.AppendLine($"({item.CompanyId},{item.EmployeeId},{item.LeavePolicyId},'{item.LeavePolicyName}','{item.EffectiveDate}','{item.EndDate}','{item.LeaveTypeIds}','{item.CreatedAt}'),");
                        }
                        Console.WriteLine(sb.ToString()[..(sb.Length - 3)] + ";");
                        Console.WriteLine("                   ");
                    }
                    Console.WriteLine("是否开始导入？[Y/N]");
                    var insert = Console.ReadLine();
                    if (insert == "Y" || insert == "y")
                    {
                        Console.WriteLine("--------------------开始导入数据--------------------");
                        int affectedRows = 0;
                        using var transaction = await connection.BeginTransactionAsync();
                        try
                        {
                            var deleteSql = "DELETE FROM time_leave_policy_employee_details WHERE employee_id = @EmployeeId";
                            var insertSql = "INSERT INTO time_leave_policy_employee_details (company_id, employee_id, leave_policy_id, leave_policy_name, effective_date, end_date, leave_type_ids, created_at) VALUES (@CompanyId, @EmployeeId, @LeavePolicyId, @LeavePolicyName, @EffectiveDate, @EndDate, @LeaveTypeIds, @CreatedAt) ";
                            foreach (var item in result)
                            {
                                using (var deleteCommand = new MySqlCommand(deleteSql, connection, transaction))
                                {
                                    deleteCommand.Parameters.AddWithValue("@EmployeeId", item.EmployeeId);
                                    await deleteCommand.ExecuteNonQueryAsync();
                                }

                                using var insertCommand = new MySqlCommand(insertSql, connection, transaction);
                                insertCommand.Parameters.AddWithValue("@CompanyId", item.CompanyId);
                                insertCommand.Parameters.AddWithValue("@EmployeeId", item.EmployeeId);
                                insertCommand.Parameters.AddWithValue("@LeavePolicyId", item.LeavePolicyId);
                                insertCommand.Parameters.AddWithValue("@LeavePolicyName", item.LeavePolicyName);
                                insertCommand.Parameters.AddWithValue("@EffectiveDate", item.EffectiveDate);
                                insertCommand.Parameters.AddWithValue("@EndDate", item.EndDate);
                                insertCommand.Parameters.AddWithValue("@LeaveTypeIds", item.LeaveTypeIds);
                                insertCommand.Parameters.AddWithValue("@CreatedAt", item.CreatedAt);
                                affectedRows += await insertCommand.ExecuteNonQueryAsync();
                            }
                            await transaction.CommitAsync();
                        }
                        catch (Exception ex)
                        {
                            await transaction.RollbackAsync();
                            throw;
                        }
                        Console.WriteLine($"--------------------导入数据完毕，共成功 {affectedRows} 条--------------------");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"error: {ex.Message}");
            }
            finally
            {
                await connection.CloseAsync();
                connection.Dispose();
            }
        }
    }

    public class Employee
    {
        public int Id { get; set; }

        public string? EmployeeNumber { get; set; }
    }

    public class Leave
    {
        public int Id { get; set; }

        public string? Code { get; set; }

        public string? Name { get; set; }

        public string? LeaveTypeIds { get; set; }
    }

    public class EmployeeLeave
    {
        public string? EmployeeNumber { get; set; }

        public string? Date { get; set; }

        public string? Code { get; set; }

        public string? EndDate { get; set; }
    }

    public class Result
    {
        /// <summary>
        /// company_id
        /// </summary>
        public int CompanyId { get; set; } = 1;

        /// <summary>
        /// employee_id
        /// </summary>
        public int EmployeeId { get; set; }

        /// <summary>
        /// leave_policy_id
        /// </summary>
        public int LeavePolicyId { get; set; }

        /// <summary>
        /// leave_policy_name
        /// </summary>
        public string? LeavePolicyName { get; set; }

        /// <summary>
        /// effective_date
        /// </summary>
        public string? EffectiveDate { get; set; }

        /// <summary>
        /// end_date
        /// </summary>
        public string? EndDate { get; set; }

        /// <summary>
        /// leave_type_ids
        /// </summary>
        public string? LeaveTypeIds { get; set; }

        /// <summary>
        /// created_at
        /// </summary>
        public string CreatedAt { get; set; } = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
    }
}